import pandas as pd
# 读取浦发银行储蓄卡流水
def PF_Bank_tolist(file):
    # 读取浦发银行pc端导出的.xls文件
    df_list = pd.read_excel(file).values.tolist()
    # 将列表中的nan值替换为空字符串
    df_list = [[str(i).replace('nan', '') for i in row] for row in df_list]
    # 对列表数据进行处理
    txt_list=[]
    for row in df_list:
        date=str(row[1]).replace('/','-')
        money=""
        if str(row[3]) == "":
            money=f"-{str(row[4])}"
        else:
            money=str(row[3])
        row_list=[str(date).strip(),str(row[2]).strip(),money.strip()]
        txt_list.append(row_list)
    return txt_list[1:]


import xlsxwriter
if __name__ == '__main__':
    file = r"E:\beancount-bookkeeping\浦发储蓄卡\浦发2024-11-23.xls"
    # df = pd.read_excel(file).values.tolist()
    # df_list = [[str(i).replace('nan', '') for i in row] for row in df]
    # for i in df_list:print(i)
    txt_list = PF_Bank_tolist(file)
    for i in txt_list: print(i)
    # 将列表导出为excel
    # 创建一个Excel文件
    workbook = xlsxwriter.Workbook('C:\\Users\\Larry\\Desktop\\output.xlsx')
    worksheet = workbook.add_worksheet()
    # 将数据写入Excel文件
    for row_num, row_data in enumerate(txt_list):
        for col_num, col_data in enumerate(row_data):
            worksheet.write(row_num, col_num, col_data)
    # 关闭工作簿
    workbook.close()

